#!/usr/bin/env python

import utiles
import servers
import MySQLdb
import MySQLdb.cursors
import string
from decimal import Decimal
import sys
import os

host=servers.host
user=servers.user
passwd=servers.passwd
db_name=servers.db_name

miBD=MySQLdb.connect(host=host,user=user,passwd=passwd,db=db_name)
miCursor=miBD.cursor()
miBDDict=MySQLdb.connect(host=host,user=user,passwd=passwd,db=db_name, cursorclass=MySQLdb.cursors.DictCursor)
miCursorDict=miBDDict.cursor()
midb=MySQLdb.connect(host=host,user=user,passwd=passwd,db=db_name)
micursor=midb.cursor()

def ordenCruda(consultaSQL):
    miCursor.execute(consultaSQL)
    auxiliar = miCursor.fetchall()
    return auxiliar

def ordenCrudaDict(consultaSQL, previo="SELECT 1"):
    miCursorDict.execute(previo)
    miCursorDict.execute(consultaSQL)
    auxiliar = miCursorDict.fetchall()    
    return auxiliar

def executa(sql):
    micursor.execute(sql)
    ultimoid = midb.insert_id()
    micursor.execute("set autocommit = 1")
    return ultimoid

# *****************************************************************************

def crearTabla():
    sql = "CREATE TABLE `erpsussan`.`saldos2` (`id` INT( 5 ) NOT NULL AUTO_INCREMENT ,`idcliente` VARCHAR( 15 ) NOT NULL ,\
    `fecha` DATE NOT NULL ,`idrecibo` VARCHAR( 15 ) NULL ,`saldoinicial` DECIMAL( 10, 2 ) NULL DEFAULT '0.00',\
    `montopagado` DECIMAL( 10, 2 ) NULL DEFAULT '0.00',PRIMARY KEY ( `id` )) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_spanish_ci"
    try:
        crear = ordenCruda(sql)
        print "Tabla creada =>",
    except:
        sql = "TRUNCATE TABLE saldos2"
        crear = ordenCruda(sql)
        print "La tabla saldos2 ya existe. Datos eliminados =>",

def recuperarValores(idcliente):
    sql = "SELECT * FROM vtas_valores2 WHERE idcliente = '%s' AND descripcion LIKE '%s'" % (idcliente,"%saldo inicial%")
    listaPagos = ordenCrudaDict(sql)
    if listaPagos == ():
        listaPagos = None
    return listaPagos

def llenarTabla():
    sql = "SELECT * FROM saldos"
    listaMigracion = ordenCrudaDict(sql)
    print "Se procesaran %s registros.\n" % (len(listaMigracion))
    
    if listaMigracion != ():
        for saldo in listaMigracion:
            listaPagos = recuperarValores(saldo['idcliente'])
            totalSaldo = saldo['saldoinicial']
            if listaPagos != None:
                total = Decimal("0.00")
                for pago in listaPagos:
                    total += pago['montopago']
                totalSaldo += total
            sql = "INSERT INTO saldos2(idcliente,fecha,saldoinicial) VALUES ('%s','2008-05-12','%s')" % (saldo['idcliente'],totalSaldo)
            resultado = executa(sql)
            if listaPagos != None:
                for pago in listaPagos:
                    fecha = pago['fecha']
                    montopagado = pago['montopago']
                    idrecibo = pago['idrecibo']
                    sql = "INSERT INTO saldos2(idcliente,fecha,idrecibo,montopagado) VALUES ('%s','%s','%s','%s')" % (saldo['idcliente'],fecha,idrecibo,montopagado)
                    resultado = executa(sql)
            print saldo['idcliente'],
    
if __name__ == '__main__':

    print "Crear tabla saldos2"
    crearTabla()
    llenarTabla()

    miCursor.close()
    miBD.close()
    micursor.close()
